package com.dshore.messagecenter.dao.statistics;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class QueuePropertyDao {
	@Autowired
	JdbcTemplate jdbcTemplate;
	
	/**
	 * 根据给定的本日字符串时间获取当日的消息发送延迟
	 * @version 8/29
	 */
	public List<Map<String, Object>>getTodayDelayByTime(String riqi){
		StringBuffer sql=new StringBuffer();
		sql.append("select aaa.a,aaa.b,bbb.c,nvl(bbb.d,1) as e, aaa.b/nvl(bbb.d,1) as f\r\n" + 
				"from \r\n" + 
				"(select TO_CHAR(CREATED_TIME,'hh24') as a,SUM(T_MR_KPI_RECORD_CURRENT.KPI_VALUE) as b \r\n" + 
				"from T_MR_KPI_RECORD_CURRENT \r\n" + 
				"WHERE T_MR_KPI_RECORD_CURRENT.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息发送延迟') \r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=\'"+riqi+"00:00:00\' \r\n" + 
				"AND  TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=\'"+riqi+"23:59:59\' \r\n" + 
				"GROUP BY TO_CHAR(CREATED_TIME,'hh24') \r\n" + 
				"ORDER BY TO_CHAR(CREATED_TIME,'hh24')) aaa \r\n" + 
				"LEFT JOIN (\r\n" + 
				"	select TO_CHAR(CREATED_TIME,'hh24') as c,SUM(T_MR_KPI_RECORD_CURRENT.KPI_VALUE) as d\r\n" + 
				"	from T_MR_KPI_RECORD_CURRENT\r\n" + 
				"	WHERE T_MR_KPI_RECORD_CURRENT.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量')\r\n" + 
				"	AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=\'"+riqi+"00:00:00\' \r\n" + 
				"	AND  TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=\'"+riqi+"23:59:59\' \r\n" + 
				"	GROUP BY TO_CHAR(CREATED_TIME,'hh24')\r\n" + 
				"	ORDER BY TO_CHAR(CREATED_TIME,'hh24')) bbb\r\n" + 
				"	on aaa.a=bbb.c\r\n" + 
				"	order by aaa.a");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	/**
	 * 根据给定的本日字符串时间获取单个主题队列的延迟
	 * 根据单个主题队列的obj的值查询他们的KPI_VALUE
	 * @Version 8/29
	 */
	public List<Map<String,Object>> getTodayDelayByTopic(String riqi) {
		StringBuffer sql=new StringBuffer();
		sql.append("select bbb.named,nvl(bbb.valued/aaa.value,0) as value from \r\n" + 
				"(select AA.TOPIC_QUEUE_NAME AS NAME,bb.value AS VALUE from \r\n" + 
				"(select top.TOPIC_QUEUE_NAME , top.topic_queue_no\r\n" + 
				"from T_MD_TOPIC top) AA,\r\n" + 
				"(select t.obj,sum(t.KPI_VALUE) as value\r\n" + 
				"from T_MR_KPI_RECORD_CURRENT t\r\n" + 
				"where t.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量')\r\n" + 
				"and t.OBJ_TYPE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='obj_type' and T_SYS_DICT.DICT_NAME='主题队列')\r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=\'"+riqi+"00:00:00\' \r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=\'"+riqi+"23:59:59\' \r\n" + 
				"GROUP BY t.obj\r\n" + 
				") BB\r\n" + 
				"WHERE AA.topic_queue_no=BB.OBJ) aaa\r\n" + 
				"right JOIN\r\n" + 
				"(select AA.TOPIC_QUEUE_NAME AS NAMEd,bb.value AS VALUEd from \r\n" + 
				"(select top.TOPIC_QUEUE_NAME , top.topic_queue_no\r\n" + 
				"from T_MD_TOPIC top) AA,\r\n" + 
				"(select t.obj,sum(t.KPI_VALUE) as value\r\n" + 
				"from T_MR_KPI_RECORD_CURRENT t\r\n" + 
				"where t.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息发送延迟')\r\n" + 
				"and t.OBJ_TYPE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='obj_type' and T_SYS_DICT.DICT_NAME='主题队列')\r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >='2018-08-23 00:00:00' \r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <='2018-08-29 23:59:59' \r\n" + 
				"GROUP BY t.obj) BB\r\n" + 
				"WHERE AA.topic_queue_no=BB.OBJ) bbb\r\n" + 
				"on aaa.name=bbb.named");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	/**
	 * 根据下拉框中option标签的id来查询过去七天的延迟数据--按照时间分组
	 * @version 8/29
	 */
	public List<Map<String,Object>> getWeekDelayByTimeByOption() {
		StringBuffer sql=new StringBuffer();
		sql.append("select aaa.day, aaa.value/bbb.valuess as value\r\n" + 
				"from（\r\n" + 
				"select days.createdTime1 as day,nvl(a,0) as value \r\n" + 
				"from(select to_char(SYSDATE-LEVEL+1,'yyyy-mm-dd') as createdTime1 from dual connect by level<=7) days \r\n" + 
				"LEFT JOIN( SELECT TO_CHAR(CREATED_TIME,'yyyy-mm-dd') AS createdTime2,Sum(KPI_VALUE) AS a FROM T_MR_KPI_RECORD_CURRENT  WHERE KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息发送延迟')  GROUP BY TO_CHAR(CREATED_TIME,'yyyy-mm-dd')) m  \r\n" + 
				"ON days.createdTime1=m.createdTime2 \r\n" + 
				"ORDER BY days.createdTime1） aaa\r\n" + 
				"LEFT JOIN (\r\n" + 
				"select days.createdTime1 as dayss,nvl(a,1) as valuess \r\n" + 
				"from(select to_char(SYSDATE-LEVEL+1,'yyyy-mm-dd') as createdTime1 from dual connect by level<=7) days \r\n" + 
				"LEFT JOIN( SELECT TO_CHAR(CREATED_TIME,'yyyy-mm-dd') AS createdTime2,Sum(KPI_VALUE) AS a FROM T_MR_KPI_RECORD_CURRENT  WHERE KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量')  GROUP BY TO_CHAR(CREATED_TIME,'yyyy-mm-dd')) m  \r\n" + 
				"ON days.createdTime1=m.createdTime2 \r\n" + 
				"ORDER BY days.createdTime1) bbb\r\n" + 
				"on aaa.day=bbb.dayss");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	/**
	 *  根据下拉框中option标签的id来查询过去七天的延迟数据--按照主题队列分组
	 *  @Version 8/29
	 */
	public List<Map<String,Object>> getWeekDelayByTopicByOption(String riqi,String qitian) {
		StringBuffer sql=new StringBuffer();
		sql.append("select bbb.named,nvl(bbb.valued/aaa.value,0) as value from \r\n" + 
				"(select AA.TOPIC_QUEUE_NAME AS NAME,bb.value AS VALUE from \r\n" + 
				"(select top.TOPIC_QUEUE_NAME , top.topic_queue_no\r\n" + 
				"from T_MD_TOPIC top) AA,\r\n" + 
				"(select t.obj,sum(t.KPI_VALUE) as value\r\n" + 
				"from T_MR_KPI_RECORD_CURRENT t\r\n" + 
				"where t.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量')\r\n" + 
				"and t.OBJ_TYPE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='obj_type' and T_SYS_DICT.DICT_NAME='主题队列')\r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=\'"+qitian+"00:00:00\' \r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=\'"+riqi+"23:59:59\' \r\n" + 
				"GROUP BY t.obj\r\n" + 
				") BB\r\n" + 
				"WHERE AA.topic_queue_no=BB.OBJ) aaa\r\n" + 
				"right JOIN\r\n" + 
				"(select AA.TOPIC_QUEUE_NAME AS NAMEd,bb.value AS VALUEd from \r\n" + 
				"(select top.TOPIC_QUEUE_NAME , top.topic_queue_no\r\n" + 
				"from T_MD_TOPIC top) AA,\r\n" + 
				"(select t.obj,sum(t.KPI_VALUE) as value\r\n" + 
				"from T_MR_KPI_RECORD_CURRENT t\r\n" + 
				"where t.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息发送延迟')\r\n" + 
				"and t.OBJ_TYPE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='obj_type' and T_SYS_DICT.DICT_NAME='主题队列')\r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=\'"+qitian+"00:00:00\' \r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=\'"+riqi+"23:59:59\' \r\n" + 
				"GROUP BY t.obj) BB\r\n" + 
				"WHERE AA.topic_queue_no=BB.OBJ) bbb\r\n" + 
				"on aaa.name=bbb.named");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	/**
	 * 根据下拉框中option标签的id来查询过去30天的延迟数据--按照时间分组
	 */
	public List<Map<String,Object>> getMonthDelayByTimeByOption() {
		StringBuffer sql=new StringBuffer();
		sql.append("select aaa.day,aaa.value/bbb.valuess as value\r\n" + 
				"from (\r\n" + 
				"select days.createdTime1 as day,nvl(a,0) as value \r\n" + 
				"from(select to_char(SYSDATE-LEVEL+1,'yyyy-mm-dd') as createdTime1 from dual connect by level<=30) days \r\n" + 
				"LEFT JOIN( SELECT TO_CHAR(CREATED_TIME,'yyyy-mm-dd') AS createdTime2,Sum(KPI_VALUE) AS a FROM T_MR_KPI_RECORD_CURRENT  WHERE KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息发送延迟')  GROUP BY TO_CHAR(CREATED_TIME,'yyyy-mm-dd')) m  \r\n" + 
				"ON days.createdTime1=m.createdTime2 \r\n" + 
				"ORDER BY days.createdTime1) aaa\r\n" + 
				"LEFT JOIN\r\n" + 
				"(select days.createdTime1 as day,nvl(a,1) as valuess \r\n" + 
				"from(select to_char(SYSDATE-LEVEL+1,'yyyy-mm-dd') as createdTime1 from dual connect by level<=30) days \r\n" + 
				"LEFT JOIN( SELECT TO_CHAR(CREATED_TIME,'yyyy-mm-dd') AS createdTime2,Sum(KPI_VALUE) AS a FROM T_MR_KPI_RECORD_CURRENT  WHERE KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量')  GROUP BY TO_CHAR(CREATED_TIME,'yyyy-mm-dd')) m  \r\n" + 
				"ON days.createdTime1=m.createdTime2 \r\n" + 
				"ORDER BY days.createdTime1) bbb\r\n" + 
				"on aaa.day=bbb.day");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	/**
	 *  根据下拉框中option标签的id来查询过去30天的延迟数据--按照主题队列分组
	 *  @Version 8/27
	 */
	public List<Map<String,Object>> getMonthDelayByTopicByOption(String riqi,String mtian) {
		StringBuffer sql=new StringBuffer();
		sql.append("select bbb.named,nvl(bbb.valued/aaa.value,0) as value from \r\n" + 
				"(select AA.TOPIC_QUEUE_NAME AS NAME,bb.value AS VALUE from \r\n" + 
				"(select top.TOPIC_QUEUE_NAME , top.topic_queue_no\r\n" + 
				"from T_MD_TOPIC top) AA,\r\n" + 
				"(select t.obj,sum(t.KPI_VALUE) as value\r\n" + 
				"from T_MR_KPI_RECORD_CURRENT t\r\n" + 
				"where t.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量')\r\n" + 
				"and t.OBJ_TYPE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='obj_type' and T_SYS_DICT.DICT_NAME='主题队列')\r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=\'"+mtian+"00:00:00\' \r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=\'"+riqi+"23:59:59\' \r\n" + 
				"GROUP BY t.obj\r\n" + 
				") BB\r\n" + 
				"WHERE AA.topic_queue_no=BB.OBJ) aaa\r\n" + 
				"right JOIN\r\n" + 
				"(select AA.TOPIC_QUEUE_NAME AS NAMEd,bb.value AS VALUEd from \r\n" + 
				"(select top.TOPIC_QUEUE_NAME , top.topic_queue_no\r\n" + 
				"from T_MD_TOPIC top) AA,\r\n" + 
				"(select t.obj,sum(t.KPI_VALUE) as value\r\n" + 
				"from T_MR_KPI_RECORD_CURRENT t\r\n" + 
				"where t.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息发送延迟')\r\n" + 
				"and t.OBJ_TYPE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='obj_type' and T_SYS_DICT.DICT_NAME='主题队列')\r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=\'"+mtian+"00:00:00\' \r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=\'"+riqi+"23:59:59\' \r\n" + 
				"GROUP BY t.obj) BB\r\n" + 
				"WHERE AA.topic_queue_no=BB.OBJ) bbb\r\n" + 
				"on aaa.name=bbb.named");
		return jdbcTemplate.queryForList(sql.toString());
	}
}
